import openpyxl
import sys
from pathlib import Path
import os
from datetime import datetime
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

def increment_column(col):
    # Convert the column letter to ASCII and increment
    col_ascii = ord(col.upper())  # Convert to uppercase to handle lowercase input
    col_ascii += 1

    # Convert back to letter
    incremented_col = chr(col_ascii)

    return incremented_col


def getTargetColumn():
    return [
        {"source_letter": "B", "width": 10, "col_name": "患者编码",'source_index':1,'result_index':0,'result_letter':'A'},
        {"source_letter": "C", "width": 10, "col_name": "患者姓名",'source_index':2,'result_index':1,'result_letter':'B'},
        {"source_letter": "D", "width": 10, "col_name": "患者性别",'source_index':3,'result_index':2,'result_letter':'C'},
        {"source_letter": "E", "width": 10, "col_name": "患者年龄",'source_index':4,'result_index':3,'result_letter':'D'},
    ]


def buildWorkBook(headers):
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    start_cell = "A"
    for index, col in enumerate(headers):
        sheet["{}{}".format(start_cell, "1")] = col["col_name"]
        sheet.column_dimensions[start_cell].width = col["width"]
        start_cell = increment_column(start_cell)

    return workbook




# append a new header column if not exist


def append_new_column_if_not_exist(workbook, header_name):
    # Select the active sheet
    sheet = workbook.active

    # Get the coordinates of the last column
    last_column = sheet.max_column
    last_column_letter = sheet.cell(row=1, column=last_column).column_letter
    next_column_letter = increment_column(last_column_letter)
    sheet[f"{next_column_letter}1"] = header_name

    return workbook


arguments = sys.argv[1:]

saving_result_dictory = ""
current_datetime = datetime.now()
formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
ext_dir = f"merged_result_{formatted_datetime}"

args_length = len(sys.argv) - 1
if args_length == 0:
    print("用法: command [directory name]\nExample: main.exe example")
    sys.exit()
directory_path = arguments[0]

if not(os.path.exists(directory_path) and os.path.isdir(directory_path)):
    print(f"The directory '{directory_path}' does not exist or is not a directory.")
    sys.exit()

project_names = []

processing_files = []

fail_files = []
# w.save(f'{ext_dir}.xlsx')
total_file_count = 0

processing_file_count = 0

source_project_name_column_letter = {"letter": "M", "index": 12}

source_dialog_result_column_letter = {"letter": "R", "index": 17}

source_result_column_letter = {"letter": "N", "index": 13}

source_unit_column_letter = {"letter": "O", "index": 14}

source_patient_code_column_letter = {"letter": "B", "index": 1}

source_patient_name_column_letter = {"letter": "C", "index": 2}

source_patient_sex_column_letter = {"letter": "D", "index": 3}

source_patient_age_column_letter = {"letter": "E", "index": 4}



def append_row_to_classified_data(row, classified_data, key,project_name):
    data = {}

    if key in classified_data:
        data = classified_data[key]

    d = "诊断"
    base_headers = []
    for c in getTargetColumn():
        base_headers.append(c['col_name'])
    base_headers.append('单位')

    for h in headers:
        if h['col_name'] not in data:
            ## set project result to coordinary column bug
            if h['col_name'] not in project_names:
                data[h['col_name']] = row[h['source_index']]
            else:
                data[project_name]  =''

    data[project_name] = row[source_result_column_letter['index']]

    source_dialog_result_index = source_dialog_result_column_letter['index']
    source_dialog_result = row[source_dialog_result_index]
    if source_dialog_result is not None:
        try:
            if len(source_dialog_result) != 0:
                split_dialog = data[d].split(';')
                split_dialog.append(source_dialog_result)
                data[d] = ';'.join(split_dialog)
            else:
                data[d] = ''
        except KeyError as e:
            data[d] = source_dialog_result


    classified_data[key] = data  


for filename in os.listdir(directory_path):
    total_file_count = total_file_count + 1
    if filename.endswith(".xlsx"):
        file_path = os.path.join(directory_path, filename)
        filename_without_extension = os.path.splitext(os.path.basename(file_path))[0]

        # Load the workbook

        # Access worksheets and perform actions

        # Create a new Excel workbook

        try:
            existing_workbook = openpyxl.load_workbook(file_path)

            # collect files has been processed
            processing_files.append(file_path)
            project_names.append(filename_without_extension)
            processing_file_count = processing_file_count + 1
        except Exception as e:
            fail_files.append(file_path)
            continue





sorted_project_names = sorted(project_names)

headers = getTargetColumn()

for index,project_name in enumerate(sorted_project_names):
    current_max_index = headers[-1]['source_index']
    current_max_letter = headers[-1]['result_letter']
    _i = current_max_index + 1
    _letter = increment_column(current_max_letter)
    headers.append({
        "col_name": project_name, 
        "width": 30,
        "result_index":_i,
        "source_index":source_project_name_column_letter['index'],
        'result_letter':_letter,
        'source_letter':source_project_name_column_letter['letter']
        })
    #sort by index asc
    headers = sorted(headers, key=lambda x: x['source_index'])

## append dialog header
current_max_index = headers[-1]['source_index']
current_max_letter = headers[-1]['result_letter']
_i = current_max_index + 1
_letter = increment_column(current_max_letter)
headers.append({
    "col_name": '诊断', 
    "width": 50,
    "result_index":_i,
    "source_index":source_dialog_result_column_letter['index'],
    'result_letter':_letter,
    'source_letter':source_dialog_result_column_letter['letter']
    })
## append unit header
current_max_index = headers[-1]['source_index']
current_max_letter = headers[-1]['result_letter']
_i = current_max_index + 1
_letter = increment_column(current_max_letter)
headers.append({
    "col_name": '单位', 
    "width": 10,
    "result_index":_i,
    "source_index":source_unit_column_letter['index'],
    'result_letter':_letter,
    'source_letter':source_unit_column_letter['letter']
    })


# for h in headers:
#     print(h)
# sys.exit()
# build the result excel first
saving_result_work_book = buildWorkBook(headers=headers)



classified_data = {}
keys = []

for sorted_project_name in sorted_project_names:
    file_path = os.path.join(directory_path, sorted_project_name)
    file_path = f"{file_path}.xlsx"
    processing_work_book = openpyxl.load_workbook(file_path)
    # # Select a sheet from the workbook
    existing_sheet = processing_work_book.active



    for row in existing_sheet.iter_rows(min_row=2, values_only=True):
        key = row[1]
        append_row_to_classified_data(row=row,classified_data=classified_data,key= key,project_name=sorted_project_name)
        if key not in keys:
            keys.append(key)
    # Dictionary to store classified data

    # Print or process the rows as needed

for k in keys:
    for pj_name in project_names:
        try:
            pj_result = classified_data[k][pj_name]
        except KeyError as e:
            classified_data[k][pj_name] = ''
        




print(f"该目录总文件数：{total_file_count} ")
print(f"处理成功文件数：{processing_file_count} ")
print("处理失败文件数：{} ".format(total_file_count - processing_file_count))
if total_file_count - processing_file_count > 0:
    print(f"处理失败文件： ")
    for file in fail_files:
        print(f"{file}\n")
sheet = saving_result_work_book.active
start_index = 2
for index,key in enumerate(keys):
    
    data = classified_data[key]

    for dkey in data:
        for h in headers:
            if h['col_name'] == dkey:
                letter = h['result_letter']
        cell = '{}{}'.format(letter,start_index)
        sheet[cell] = data[dkey]
        for ce in sheet[letter]:
            ce.alignment = Alignment(wrap_text=True)
    start_index = start_index + 1

save_name = '{}.xlsx'.format(ext_dir)
saving_result_work_book.save(save_name)

print(f"处理成功，文件名： {save_name} ")